package com.yj.transport.mapper;

import com.yj.transport.entity.TransportInfo;
import com.yj.transport.entity.TransportJournal;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * Created by zhengguanyong on 2017/12/6.
 */

@Repository
@Mapper
public interface TransportInfoMapper {

    @Insert("INSERT INTO transport_info " +
            "(`main_id`, `user_id`, `user_phone`, `transport_type`, `plate_number`, `plate_number_all`, `goods_weight`, `amount`, `start_place`,`start_longitude`, `start_latitude`, `end_place`,`end_longitude`, `end_latitude`, `deliver_time`, `ownership`,`arrive_place`,`arrive_time`,`longitude`,`latitude`,`place_type`,`update_time`, `create_time`) " +
            "VALUES(#{transportInfo.main_id}, #{transportInfo.user_id}, #{transportInfo.user_phone}, " +
            "#{transportInfo.transport_type}, #{transportInfo.plate_number}, #{transportInfo.plate_number_all}, " +
            "#{transportInfo.goods_weight}, #{transportInfo.amount}, #{transportInfo.start_place},#{transportInfo.start_longitude},#{transportInfo.start_latitude}, " +
            "#{transportInfo.end_place},#{transportInfo.end_longitude},#{transportInfo.end_latitude}, #{transportInfo.deliver_time}, #{transportInfo.ownership},#{transportInfo.arrive_place},#{transportInfo.arrive_time},#{transportInfo.longitude},#{transportInfo.latitude},#{transportInfo.place_type}, now(), now())")
    @Options(useGeneratedKeys = true,keyProperty="transportInfo.id")
    void createTransportInfo(@Param("transportInfo") TransportInfo transportInfo);

    @Select("select * from transport_info where id=#{id} limit 1")
    TransportInfo getTransportInfoDetailById(@Param("id") Integer id);

    @Select("select * from transport_info where main_id=#{main_id} order by id")
    List<TransportInfo> getTransportInfoListByMainId(@Param("main_id") String main_id);

    @Update("update transport_info set ownership=#{ownership} where id=#{id}")
    int updateOwnership(@Param("id") Integer id, @Param("ownership") String ownership);

    @Select("select count(*) from transport_info where main_id=#{main_id} and transport_status=0")
    int isAllAcceptanceDone(@Param("main_id") String main_id);

    @Select("select ifnull(sum(goods_weight),0) from transport_info where main_id=#{main_id}")
    BigDecimal sumGoodsWeight(@Param("main_id") String main_id);

    @Select("<script>" +
            "SELECT sum(goods_weight) FROM transport_info " +
            "WHERE main_id IN " +
            "(SELECT main_id FROM contract " +
            "WHERE user_id IN " +
            "<foreach item = 'item' collection = 'list' open = '(' separator = ', ' close = ') '>" +
            " #{item}" +
            "</foreach>" +
            ")" +
            "</script>")
    BigDecimal sumGoodsWeightForUsers(@Param("list") List<String> list);

    @Select("select ifnull(sum(amount),0) from transport_info where main_id=#{main_id}")
    BigDecimal sumAmount(@Param("main_id") String main_id);

    @Update("update transport_info set transport_status=1 where id=#{id} and transport_status=0")
    int updateTransportStatus(@Param("id") Integer id);

    @Update("update transport_info set transport_status=2,acceptance_status=#{acceptance_status},ownership=#{ownership} where id=#{id} and transport_status=1")
    int goodsAcceptance(@Param("id") Integer id, @Param("acceptance_status") int acceptance_status, @Param("ownership") String ownership);

    @Update("update transport_info set arrive_place=#{tj.arrive_place},arrive_time=#{tj.arrive_time},longitude=#{tj.longitude},latitude=#{tj.latitude},place_type=#{tj.place_type},update_time=#{tj.arrive_time} where id=#{tj.transport_info_id}")
    int modifyCurrentPlace(@Param("tj") TransportJournal tj);

    @Select("select * from transport_info where transport_status=0 and arrive_place !='' and user_id=#{user_id} limit 50")
    List<TransportInfo> getDynamicInfoListByUserId(@Param("user_id") String user_id);

    @Select("<script>" +
            "select * from transport_info " +
            "where transport_status=0 and arrive_place !='' and user_id IN " +
            "<foreach item = 'item' collection = 'userIDList' open = '(' separator = ', ' close = ') '>" +
            "#{item}" +
            "</foreach>" +
            " limit 50" +
            "</script>")
    List<TransportInfo> getDynamicInfoListByUserIdList(@Param("userIDList") List<String> userIDList);

    @Select("select * from transport_info where transport_status=0 and arrive_place !='' limit 50")
    List<TransportInfo> getDynamicInfoListByNone();

    @Select("select plate_number from transport_info where plate_number like '%' #{plate_number} '%' order by id desc limit 5")
    List<String> searchDynamicInfoByPlateNumber(@Param("plate_number") String plate_number);

    @Select("select * from transport_info where plate_number=#{plate_number} limit 1")
    TransportInfo getDynamicInfoByPlateNumber(@Param("plate_number") String plate_number);

    @Select("select count(*) from transport_info where transport_status=0 and transport_type=#{transport_type} and arrive_place !=''")
    int countDynamicInfoGuest(@Param("transport_type") int transport_type);

    @Select("select ifnull(sum(goods_weight),0) from transport_info where transport_status=0 and arrive_place !=''")
    int sumGoodsWeightDynamicInfoGuest();

    @Select("select count(*) from transport_info where transport_status=0 and transport_type=#{transport_type} and arrive_place !='' and user_id=#{user_id}")
    int countDynamicInfo(@Param("user_id") String user_id, @Param("transport_type") int transport_type);

    @Select("select ifnull(sum(goods_weight),0) from transport_info where transport_status=0 and arrive_place !='' and user_id=#{user_id}")
    int sumGoodsWeightDynamicInfo(@Param("user_id") String user_id);

    @Select("<script>" +
            "select count(*) from transport_info " +
            "where transport_status=0 and transport_type=#{transport_type} and arrive_place !='' and user_id IN " +
            "<foreach item = 'item' collection = 'userIDList' open = '(' separator = ', ' close = ') '>" +
            "#{item}" +
            "</foreach>" +
            "</script>")
    int countDynamicInfoLeader(@Param("userIDList") List<String> userIDList, @Param("transport_type") int transport_type);

    @Select("<script>" +
            "select ifnull(sum(goods_weight),0) from transport_info " +
            "where transport_status=0 and arrive_place !='' and user_id IN " +
            "<foreach item = 'item' collection = 'userIDList' open = '(' separator = ', ' close = ') '>" +
            "#{item}" +
            "</foreach>" +
            "</script>")
    int sumGoodsWeightDynamicInfoLeader(@Param("userIDList") List<String> userIDList);

    @Select("select ti.* from transport_info ti,oa_user_info oi where oi.corp=#{corp} and oi.role_level=1 and oi.user_id=ti.user_id and ti.transport_status=0 and ti.arrive_place !='' order by ti.id desc limit 50")
    List<TransportInfo> getDynamicInfoListByCorp(@Param("corp") String corp);

    @Select("select count(*) from transport_info ti,oa_user_info oi where oi.corp=#{corp} and oi.role_level=1 and oi.user_id=ti.user_id and ti.transport_status=0 and transport_type=#{transport_type} and ti.arrive_place !='' ")
    int countDynamicInfoByCorp(@Param("corp") String corp, @Param("transport_type") int transport_type);

    @Select("select ifnull(sum(ti.goods_weight),0) from transport_info ti,oa_user_info oi where oi.corp=#{corp} and oi.role_level=1 and oi.user_id=ti.user_id and ti.transport_status=0 and ti.arrive_place !=''")
    int sumGoodsWeightDynamicInfoByCorp(@Param("corp") String corp);

    @Select("select ti.* from transport_info ti,oa_user_info oi where oi.division=#{division} and oi.role_level=1 and oi.user_id=ti.user_id and ti.transport_status=0 and ti.arrive_place !='' order by ti.id desc limit 50")
    List<TransportInfo> getDynamicInfoListByDivision(@Param("division") String division);

    @Select("select count(*) from transport_info ti,oa_user_info oi where oi.division=#{division} and oi.role_level=1 and oi.user_id=ti.user_id and ti.transport_status=0 and transport_type=#{transport_type} and ti.arrive_place !='' ")
    int countDynamicInfoByDivision(@Param("division") String division, @Param("transport_type") int transport_type);

    @Select("select ifnull(sum(ti.goods_weight),0) from transport_info ti,oa_user_info oi where oi.division=#{division} and oi.role_level=1 and oi.user_id=ti.user_id and ti.transport_status=0 and ti.arrive_place !=''")
    int sumGoodsWeightDynamicInfoByDivision(@Param("division") String division);

    @Select("select ti.* from transport_info ti,oa_user_info oi where oi.team=#{team} and oi.role_level=1 and oi.user_id=ti.user_id and ti.transport_status=0 and ti.arrive_place !='' order by ti.id desc limit 50")
    List<TransportInfo> getDynamicInfoListByTeam(@Param("team") String team);

    @Select("select count(*) from transport_info ti,oa_user_info oi where oi.team=#{team} and oi.role_level=1 and oi.user_id=ti.user_id and ti.transport_status=0 and transport_type=#{transport_type} and ti.arrive_place !='' ")
    int countDynamicInfoByTeam(@Param("team") String team, @Param("transport_type") int transport_type);

    @Select("select ifnull(sum(ti.goods_weight),0) from transport_info ti,oa_user_info oi where oi.team=#{team} and oi.role_level=1 and oi.user_id=ti.user_id and ti.transport_status=0 and ti.arrive_place !=''")
    int sumGoodsWeightDynamicInfoByTeam(@Param("team") String team);

    @Select("select * from transport_info where transport_status=0 and transport_type=3")
    List<TransportInfo> getShipTransportInfo();

}
